﻿using CallCenter.Data.Base;
using CallCenter.Data.BizUtilities.Base;
using CallCenter.Data.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CallCenter.Data.BizUtilities
{
    public class ProdutRequestMemeberUtility : BaseUtility
    {
        public ProdutRequestMemeberUtility(DataManager manager)
            : base(manager)
        { }


        //assign placeholder for the values in the table to insert
        public int Insert(int productRequestId, int contactId, string description)
        {
            int nRtn = -1;
            string sql = "INSERT INTO ddlProductRequestMember" +
                 "(productRequestId, contactId, description)" +
             "VALUES(@productRequestId, @contactId, @description)" +
             "SELECT @pResult =@@IDENTITY";


            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@productRequestId", SqlDbType.Int, 10, productRequestId);
                this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 10, contactId);
                this.Factory.AddParameter(cmd, "@description", SqlDbType.NVarChar, 100, description);
              
                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }

            return nRtn;
        }

        public DataTable GetContactId(int invoiceId)
        {
           string sql = @"SELECT prm.* FROM ddlProductRequestMember prm
                                      Where prm.productRequestId = " + invoiceId;

            return this.Factory.ExecuteTable(sql);
        }

        public int Update(int productRequestId, int contactId, string description)
        {
            int nRtn = -1;
            string sql = "UPDATE ddlProductRequestMember" +
                 "SET productRequestId = @productRequestId, contactId=@contactId, description)" +
             "VALUES(@productRequestId, @contactId, @description)" +
             "SELECT @pResult =@@IDENTITY";


            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@productRequestId", SqlDbType.Int, 10, productRequestId);
                this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 10, contactId);
                this.Factory.AddParameter(cmd, "@description", SqlDbType.NVarChar, 100, description);

                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }

            return nRtn;
        }

        internal void DeleteProductRequestMemberByProductId(int ProductId)
        {
            string sql = "DELETE FROM ddlProductRequestMember " +
                  " WHERE productRequestId = @commId ";


            using (var cmd = this.Factory.GetCommand(sql))
            {

                this.Factory.AddParameter(cmd, "@commId", SqlDbType.Int, 4, ProductId);
                this.Factory.ExecuteNonQuery(cmd);
            }

        }





        public DataTable GetId(int prodId)
        {
            string sql= @"SELECT contactId FROM ddlProductRequestMember prm
                                      Where prm.productRequestId = " + prodId;

            return this.Factory.ExecuteTable(sql);

        }
    }
}

        